<?php define('SIDEBAR', FALSE);?>
<?php /* 
Template Name: (02) Page with results 
*/ ?>
<?php get_header(); ?>

	<?php // Start spesial pagecontent
	global $wpdb;   // Give wpdb with global scope
	$orderby_array = array('timetotal' => 'Tid totalt', 'timeswim' => 'Svømming', 'timebike' => 'Sykkel', 'timerun' => 'Løp');
	$orderkey_array = array('asc', 'desc');
	$sql_competition = 'SELECT id, name from competition order by id asc';
	$competition_array = $wpdb->get_results($sql_competition);
	//echo "<pre> " . print_r($competition_array[0]->{'name'}) . "</pre>";
	// Check for $_GET variables
	if (isset($_GET['theyear']) and is_numeric($_GET['theyear'])) {
    	$theyear = (int)$_GET['theyear'];
	} else {
		if(isset($_GET['theyear']) and $_GET['theyear']== "all") {
			$allyears = true; 
		} else {
			$theyear = date('Y');
		}
		
	}
	if (isset($_GET['competition']) and is_numeric($_GET['competition'])) {
		$competition = (int)$_GET['competition'];
	} else {
		$allcompetitions = true;
	}
	if (isset($_GET['sex']) and is_numeric($_GET['sex'])) {
		$tmp_sex = (int)$_GET['sex'];
		if($tmp_sex < 2 and $tmp_sex > -1) $sex = $tmp_sex;
	} else {
		//$sex = -1;
	}
	if (isset($_GET['orderby']) and array_key_exists($_GET['orderby'], $orderby_array)) {
    	$orderby = $_GET['orderby'];
	} else {
		$orderby = (string)key($orderby_array);
	}
	if (isset($_GET['orderkey']) and $_GET['orderkey'] == $orderkey_array[1]) {
    	$orderkey = $orderkey_array[1];
	} else {
		$orderkey = $orderkey_array[0];
	}
	?>
	
	<?php
	global $wpdb;   // Give wpdb with global scope
	$sex_query = "";
	$competition_query = "";
	$year_query = "record.$orderby != ''";
	if(isset($sex)) {
		$sex_query = "and sex = $sex";
	}
	if(isset($competition)) {
		//$competition_id = $competition_array[$competition]->{'id'};
		$competition_query = "and competition.id = $competition";
	}
	if(isset($theyear)) {
		$year_query = "record.year = $theyear";
	}
	$sql = "SELECT record.id, record.timeswim, record.timerun, record.timebike, 
	record.timetotal, record.year, competition.name as competition, competition.id, 
	athlete.name as athlete, athlete.team as team, athlete.sex as sex
		FROM record 
		left join athlete on record.athlete_id = athlete.id 
		left join competition on record.competition_id = competition.id 
		"; 
	
	if(isset($_GET['query']) and trim($_GET['query']) != "" ) {
		$clean_string = htmlspecialchars(trim($_GET['query']));
		$sql .= "WHERE athlete.name LIKE '%$clean_string%' OR athlete.team LIKE '%$clean_string%' order by record.year desc";
	} else {
		$sql .= "WHERE $year_query $competition_query $sex_query order by record.$orderby $orderkey ";
	}
	
	//echo "<pre>" . $sql . "</pre>";
	$results = $wpdb->get_results($sql);  // Run our query, getting results as an object
	// Get all years
	$sql_year = 'SELECT distinct year from record order by year desc';
	$all_years = $wpdb->get_results($sql_year);
	// Get all years
	//$sql_year = 'SELECT distinct year from record order by year desc';
	//$all_years = $wpdb->get_results($sql_year);
?>

<form id="result_search" method="get" action="<?php echo htmlspecialchars($_SERVER['SCRIPT_URI']); ?>">
	<label for="theyear">Årstall</label> 
	<select name="theyear" id="theyear">
		<option value="all"<?php if(isset($allyears)) echo ' selected="selected"'; ?>>Vis alle</option>
	<?php foreach($all_years as $ayear): ?>
		<option value="<?php echo $ayear->year; ?>"<?php if($ayear->year == $theyear) echo ' selected="selected"'; ?>><?php echo $ayear->year; ?></option>
	<?php endforeach; ?>
	</select>
	
	<label for="competition">Stevne</label> 
	<select name="competition" id="competition">
		<option value="all"<?php if(isset($allcompetitions)) echo ' selected="selected"'; ?>>Vis alle</option>
	<?php foreach($competition_array as $comp): ?>
		<option value="<?php echo $comp->id; ?>"<?php if(isset($competition) and $comp->id == $competition) echo ' selected="selected"'; ?>><?php echo $comp->name; ?></option>
	<?php endforeach; ?>
	</select>



<label for="orderby">Sortert etter</label> 
<select name="orderby" id="orderby">
	<?php foreach($orderby_array as $key => $value): ?>
	<option value="<?php echo $key; ?>"<?php if($key == $orderby) echo ' selected="selected"'; ?>><?php echo $value; ?></option>
	<?php endforeach; ?>

</select>
<label for="orderkey">Rekkefølge</label> 
<select name="orderkey" id="orderkey">
	<option value="asc">Stigende</option> 
	<option value="desc"<?php if($orderkey_array[1] == $orderkey) echo ' selected="selected"'; ?>>Synkende</option>
</select>
<label for="sex">Kjønn</label> 
<select name="sex" id="sex">
	<option value="-1">Begge</option> 
	<option value="0"<?php if(isset($sex) and $sex == 0) echo ' selected="selected"'; ?>>Menn</option>
	<option value="1"<?php if(isset($sex) and $sex == 1) echo ' selected="selected"'; ?>>Kvinner</option>
</select>
<input type="submit" value="Bla i resultatene">
</form>
<form id="result_search_name" method="get" action="<?php echo htmlspecialchars($_SERVER['SCRIPT_URI']); ?>">
	<label for="query">Søk på navn eller klubb</label>
	<input type="text" name="query" value="<?php if( isset($clean_string)) echo $clean_string ?>" id="query" size="30">
	<input type="submit" value="Søk">
</form>
<p class="search-information">Det ble funnet <?php echo sizeof($results); ?> resultater i ditt søk.</p>

<table class="<?php echo $orderby; ?>">
		<tr>
			<th>navn</th>
			<th>klubb</th>
			<th>sted</th>
			<th>år</th>
			<th class="timeswim">Svømming</th>
			<th class="timebike">Sykkel</th>
			<th class="timerun">Løp</th>
			<th class="timetotal">Totalt</th>
		</tr>
	<?php $i = 0; ?>
	<?php foreach($results as $result): ?>
		<tr <?php if($i%2 == 0) echo 'class="altRow"'; $i++; ?> >
			<td class="name"><?php echo $result->athlete; ?></td>
			<td><?php echo $result->team; ?></td>
			<td><?php echo $result->competition; ?></td>
			<td><?php echo $result->year; ?></td>
			<td class="timeswim"><?php echo $result->timeswim; ?></td>
			<td class="timebike"><?php echo $result->timebike; ?></td>
			<td class="timerun"><?php echo $result->timerun; ?></td>
			<td class="timetotal"><?php echo $result->timetotal; ?></td>
	</tr>
	<?php endforeach; ?>
	
	</table>

<?php get_footer(); ?>